（高级篇）MySQL 并发控制—— MVCC 与锁

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 11k | 阅读时长 ≈ 16 分钟

序言

  从前文我们知道，在事务运行过程中，数据库设置的隔离级别不同，解决的并发问题也不同。
  那么思考一个问题：这些隔离级别在内部到底是如何解决并发问题的呢？
  在数据库系统中，是通过 MVCC 和锁机制来解决该问题的。

  那么，下面来详细了解下它们吧！

MVCC

  MVCC（Multi-Version Concurrency Control），即多版本并发控制，指在RC、RR两种隔离级别下，事务在执行普通的 SELECT 操作时访问记录的版本链的过程。

作用

  MVCC 用于解决一些并发问题，最终提高并发情况的性能：

  • 读-读冲突：在传统的锁机制下，多个事务可以同时读取同一行数据，但是如果有一个事务正在修改数据，则其他事务的读操作应该等待。MVCC 允许多个事务并发读取数据的不同版本，从而解决了读-读冲突的问题，提高了并发性能
  • 读-写冲突：在传统的锁机制下，读操作和写操作会发生冲突，即一个事务正在写入数据时，其他事务无法读取该数据。这会导致并发性能下降，MVCC 通过允许读操作与写操作并发进行，解决了读-写冲突的问题
  • 写-写冲突：传统的锁机制还会导致写操作之间的冲突，即多个事务同时试图修改同一行数据，只有一个事务能成功，其他事务需要等待。MVCC 通过使用版本号或时间戳来跟踪数据的不同版本，每个事务操作的是数据的一个特定版本，从而解决了写-写冲突的问题

  MVCC 支持一致性读取，即读取操作会看到一个一致性的数据库快照。读操作会根据事务开始的时间或快照版本确定可见的数据版本，而不会受到后续并发事务的修改影响。这样可以保证读操作的一致性，避免了脏读或不可重复读等问题。

数据库差异

  不仅是 MySQL，包括 Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC，但各自的实现机制不尽相同，因为 MVCC 没有一个统一的实现标准，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

原理

  使用版本链。

版本链

  版本链是数据修改时出现的对应数据的一串 undo-log 链表。
  下面，我们通过一个例子了解它。

快速入门

  对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：

  • trx_id：每次对某条聚簇索引记录进行改动时，都会把对应的事务 id 赋值给trx_id隐藏列。
  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，该隐藏列相当于一个指针，通过它可以找到该记录修改前的信息。

  下面的t表现在只包含一条记录：

1
2
3
4
5
6
7
mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 刘备 |
+----+--------+
1 row in set (0.01 sec)

  假设插入该记录时的trx_id（事务 id）为 80，那么此刻该条记录的示意图如下所示：

  假设之后两个trx_id分别为 100、200 的事务对这条记录进行 UPDATE 操作，操作流程如下表：

时间 trx_id:100 trx_id:200
T1 BEGIN;
T2 BEGIN;
T3 UPDATE t SET u = '关羽' WHERE id = 1
T4 UPDATE t SET u = '张飞' WHERE id = 1
T5 COMMIT;
T6 UPDATE t SET u = '赵云' WHERE id = 1
T7 UPDATE t SET u = '诸葛亮' WHERE id = 1
T8 COMMIT;

  每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性，将这些undo日志都连起来就形成了一个链表，所以现在的情况就像下图一样：

  版本链的头节点存储了当前记录的最新值，每次对该记录更新后，都会将旧值放到一条undo日志中，即该记录的一个旧版本。随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，这个链表就被称之为版本链。

  另外，每个版本中还包含生成该版本时对应的trx_id，这个信息很重要，因为我们稍后就会用到。

ReadView

  对于不同隔离级别的事务来说，读取记录的方式也不同：

隔离级别 读取记录方式
READ UNCOMMITTED 直接读取记录的最新版本
READ COMMITTED 使用版本链
REPEATABLE READ 使用版本链
SERIALIZABLE 加锁后访问记录

  RC和RR都使用到了版本链，相同点是它们都需要借助ReadView判断版本链中的哪个版本为当前事务可见，不同点的是它们ReadView是否能重复生成。

  ReadView即读快照，其中主要存储了当前系统中还活跃的读写事务，这些活跃事务的trx_id将放到一个名为m_ids的列表中。
  因此，在访问某条记录时，只需要按以下步骤即可判断记录的某个版本是否可见：

  • 若被访问版本的trx_id属性值小于m_ids列表中最小的事务 id，表明生成该版本的事务在生成ReadView前已经提交，因此该版本可以被当前事务访问
  • 若被访问版本的trx_id属性值大于m_ids列表中最大的事务 id，表明生成该版本的事务在生成ReadView后才生成，因此该版本不可以被当前事务访问
  • 若被访问版本的trx_id属性值在m_ids列表中最大的事务 id 和最小事务 id 之间，那就需要判断一下trx_id属性值是不是在m_ids列表中：
    • 若存在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可被访问
    • 若不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

  如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。
  如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

  在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同，我们来看一下。

RC 场景

  在READ COMMITTED隔离级别下，每次读取数据前都将生成一个ReadView。
  我们先回忆一下，该隔离级别解决了什么并发问题。
  脏读，没错，该隔离级别不允许脏读。
  那么为何能解决脏读呢？
  因为未提交（被回滚）的事务操作涉及的数据记录对其他事务不可见。
  下面通过一个例子来解释一下：
  比方说现在系统里有两个trx_id分别为 100、200 的事务正在执行：

1
2
3
4
5
6
# trx_id 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;
1
2
3
4
5
# trx_id 200
BEGIN;

# 更新了一些别的表的记录
...

  此刻，t表中 id 为1 的记录得到的版本链表如下所示：

  假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

1
2
3
4
5
# 使用 READ COMMITTED 隔离级别的事务
BEGIN;

# SELECT 1：trx_id 为 100、200 未提交
SELECT * FROM t WHERE id = 1; # 得到的列 c 的值为'刘备'

  SELECT 1的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表内容为[100, 200]
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c的内容是'张飞'，该版本的trx_id值为100，存在于m_ids列表，因此不符合可见性要求，根据roll_pointer跳到下一个版本
  • 下一个版本的列c的内容是'关羽'，该版本的trx_id值也为100，也在m_ids列表内，因此也不符合要求，继续跳到下一个版本。
  • 下一个版本的列c的内容是'刘备'，该版本的trx_id值为80，小于m_ids列表中最小的trx_id值100，因此这个版本是符合要求的，那么最后返回给用户的版本就是这条列c为'刘备'的记录

  这是事务 100 和 200 还未提交时的 SELECT情况，现在我们仅仅提交一下事务 100 ：

1
2
3
4
5
6
7
8
# trx_id 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;

COMMIT;

  然后在trx_id为 200 的事务中更新一下t表中 id 为 1 的记录：

1
2
3
4
5
6
7
8
# trx_id 200
BEGIN;

# 更新了一些记录

UPDATE t SET c = '赵云' WHERE id = 1;

UPDATE t SET c = '诸葛亮' WHERE id = 1;

  此刻，t表中 id 为 1 记录的版本链就长这样：

  然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个 id 为 1 的记录：

1
2
3
4
5
6
7
8
# 使用 READ COMMITTED 隔离级别的事务
BEGIN;

# SELECT 1：trx_id 100、200 均未提交
SELECT * FROM t WHERE id = 1; # 得到的列 c 的值为'刘备'

# SELECT 2：trx_id 100 提交，trx_id 200 未提交
SELECT * FROM t WHERE id = 1; # 得到的列 c 的值为'张飞'

  SELECT 2的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[200]（trx_id为100的那个事务已经提交了，所以生成快照时就没有它了）
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c的内容是'诸葛亮'，该版本的trx_id值为200，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本
  • 下一个版本的列c的内容是'赵云'，该版本的trx_id值为200，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本
  • 下一个版本的列c的内容是'张飞'，该版本的trx_id值为100，比m_ids列表中最小的trx_id值200还要小，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c为'张飞'的记录

  以此类推，如果之后trx_id为200的记录也提交了，再此在使用READ COMMITTED隔离级别的事务中查询表t中id值为1的记录时，得到的结果就是'诸葛亮'了，具体流程我们就不分析了。
  总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

RR 场景

  在REPEATABLE READ隔离级别下，只会在第一次执行查询语句时生成一个ReadView，之后的查询不会重复生成。由于在同一个事务中，多次SELECT操作只会生成一个ReadView，所以自然就解决了不可重复读下的数据重复读取问题。

小结

  MVCC 只在RC、RR两个隔离级别下工作，都通过ReadView来判断事务版本信息，只会读取版本链中的最新已提交事务的 undo 日志。
  MVCC 可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
  RC、RR两个隔离级别的一个很大不同就是生成ReadView的时机不同：

  • RC会在每一次进行普通SELECT操作前都生成一个ReadView
  • RR只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复该ReadView

  其他两个隔离级别都和 MVCC 不兼容，因为RU总是读取最新的数据行，而不是符合当前事务版本的数据行；SERIALIZABLE则会对所有读取的行都加锁。

扩展——查询再插入再查询操作下的数据读取数

  思考一个问题，如果一种表存在 4 条数据，我们开启一个事务后执行以下操作：

  • ① 查询全表数据
  • ② 插入一条数据
  • ③ 查询全表数据

  那么，第 ③ 步会查询到几条数据？

  答案是 5 条数据。

  具体过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM test;
+----+------+
| a  | b    |
+----+------+
|  1 |    4 |
|  6 |    5 |
|  7 |    5 |
| 17 |    5 |
+----+------+
4 rows in set (0.00 sec)

mysql> INSERT INTO `osier`.`test` (`a`, `b`) VALUES (20, 5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+
| a  | b    |
+----+------+
|  1 |    4 |
|  6 |    5 |
|  7 |    5 |
| 17 |    5 |
| 20 |    5 |
+----+------+
5 rows in set (0.00 sec)

InnoDB 中的读

  在 MySQL 中，支持三种类型的读语句：

  • 一致性（无锁）读
  • 半一致性读
  • 锁定读

不同类型的读

一致性（无锁）读

   一致性（无锁）读，亦称普通读，快照读，英文名：Consistent Read）：指普通的SELECT读语句，即在SELECT语句末尾不加FOR UPDATE或者LOCK IN SHARE MODE的SELECT语句。

  普通读的执行方式是生成ReadView直接利用 MVCC 机制来进行读取数据，并不会对记录进行加锁。

注意事项

  注意哦，一致读取对某些 DDL 语句不起作用，比如说：

  • 对DROP TABLE操作不起作用，因为 MySQL 无法使用已被丢弃的表，而且 InnoDB 会销毁表
  • 对ALTER TABLE操作不起作用，因为 ALTER TABLE 操作会创建原始表的临时副本，并在创建临时副本时删除原始表。在事务中重新发出一致读时，新表中的行不可见，因为在事务快照时这些行还不存在。在这种情况下，事务会返回错误：ER_TABLE_DEF_CHANGED，”表定义已更改，请重试事务”

  对于 INSERT INTO … 等子句中的选择，读取类型有所不同。select、update …(SELECT) 和 CREATE TABLE …SELECT 等子句中的选择类型有所不同，这些子句没有指定 FOR UPDATE 或 LOCK IN SHARE MODE：

  • 默认情况下，InnoDB 会在这些语句中使用更强的锁，SELECT 部分的读取行为类似于 RC 下的 MVCC，在这种情况下，即使在同一个事务中，每次一致的读取都会设置并读取自己的新快照
  • 要在这种情况下执行无锁读取，请启用innodb_locks_unsafe_for_binlog选项，并将事务的隔离级别设置为 RU、RC 或 RR，以避免对从所选表读取的记录设置锁

半一致性读

  半一致性读（英文名：Semi-Consistent Read）：这是一种夹在普通读和锁定读之间的一种读取方式。它只在READ COMMITTED隔离级别下（或者在开启了innodb_locks_unsafe_for_binlog系统变量的情况下）使用UPDATE语句时才会使用。具体的含义就是当UPDATE语句读取已经被其他事务加了锁的记录时，InnoDB会将该记录的最新提交的版本读出来，然后判断该版本是否与UPDATE语句中的WHERE条件相匹配，如果不匹配则不对该记录加锁，从而跳到下一条记录；如果匹配则再次读取该记录并对其进行加锁。这样子处理只是为了让UPDATE语句尽量少被别的语句阻塞。

  注意：半一致性读只适用于对聚簇索引记录加锁的情况，并不适用于对二级索引记录加锁的情况。

  另外，MySQL 官方文档并没有对半一致性读的单独说明，算是一致性读的一种特例。

锁定读

  锁定读（英文名：Locking Read）：指SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE这种读语句，在事务读取记录之前，必须先获取该记录对应的锁。当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素。

注意事项

  外层语句中的锁定读取子句不会锁定嵌套子查询中表的记录，除非在子查询中也指定了锁定读取子句。

  例如，以下语句不会锁定表 t2 中的记录。

1
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

  因此，若要锁定表 t2 中的记录，请在子查询中也添加锁定读取子句：

1
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

读在不同隔离级别的转换

  对于SERIALIZABLE隔离级别来说，如果系统变量autocommit被设置为OFF，那普通读的语句会转变为锁定读，和在普通的SELECT语句后边加LOCK IN SHARE MODE达成的效果一样。

疑问：RR 隔离级别下真的能避免幻读嘛？

  我们在讨论问题之前，先回忆下幻读是什么。
  幻读指某个事务突然看到了一个它以前没有见过的数据行。比如说，若某个事务刚执行完一条SELECT语句就有另一个事务插入了一个新数据行，前一个事务再次执行同一条SELECT语句时，就可能多看到一个新的数据行，这就是幻读。

  在 RR 隔离级别下，一致性读看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读。 但是，对于执行锁定读、更新或者删除语句时，还是会看到数据库的最新状态，比如新插入的数据行，修改的数据行。

  下面通过一个例子来复现这种情况：

SESSION 1:

1
2
3
4
5
6
7
8
9
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

SESSION 2:

1
2
3
mysql> update tb1 set c1=101 where id =1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select id,c1 from tb1 where id=1 LOCK IN SHARE MODE;
+----+------+
| id | c1   |
+----+------+
|  1 |  101 |
+----+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

mysql> update tb1 set c1=c1+1000 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 | 1101 |
+----+------+
1 row in set (0.00 sec)

  上面 update 的行为违反了 RR 的承诺，看到了事务开始后其它事务的并发更新。这对应用开发需要特别注意，这种情况下其它数据库通常都是报错的。

  扯了这么多，我们如何避免遇到这个并发更新问题呢？

  答案很简单，修改时加WHERE条件。

InnoDB 中的锁

  在 MySQL InnoDB 存储引擎中，使用的锁存在以下类型：

  • 共享Shared锁和排他Exclusive锁
  • 意向Intention锁
  • 记录Record锁
  • 间隙Gap锁
  • 临键Next-Key锁
  • 插入意向Insert Intention锁
  • 自增AUTO-INC锁
  • 空间索引的谓词锁（略）

共享锁和排他锁

  InnoDB 实现了标准的行级锁，有两种类型的锁，共享（S）锁和排他（X）锁。

  • 共享（S）锁：允许持有该锁的事务读取一条记录
  • 排他（X）锁：允许持有该锁的事务更新或删除一条记录

  如果事务 T1 持有 r 行的共享（S）锁，那么来自不同事务 T2 对 r 行的锁的请求将被如下处理：

  • T2 对 S 锁的请求可以被立即批准。结果是，T1 和 T2 都持有 r 的 S 锁
  • T2 对 X 锁的请求不能被立即批准

  如果一个事务 T1 在行 r 上持有一个排他（X）锁，那么某个不同的事务 T2 对 r 的任一类型的锁的请求不能立即被批准。相反，事务 T2 必须等待事务 T1 释放其对 r 行的锁。

意向锁

  InnoDB支持多粒度锁，允许行锁和表锁共存。例如，一个诸如LOCK TABLES ...WRITE这样的语句在指定的表上取得了一个排他锁（一个X锁）。为了使多个粒度级别的锁切实可行，InnoDB 使用意向锁。

  意向锁是表级别的锁，它表明事务以后需要对表中的某一行进行哪种类型的锁（共享或排他）。

  在 MySQL 中，存在两种类型的意向锁：

  • 意向共享锁（IS）：表示事务打算对表中的个别行设置一个共享锁
  • 意向排他锁（IX）：表示一个事务打算在表中的个别行上设置一个排他锁

  例如，SELECT ... LOCK IN SHARE MODE设置了一个 IS 锁，SELECT ... FOR UPDATE设置了一个 IX 锁。

  意向锁协议如下：

  • 在一个事务可以获得表内某行的共享锁之前，它必须首先获得该表的 IS 锁或更强的锁
  • 在一个事务可以获得表中某行的排他锁之前，它必须首先获得该表的 IX 锁

  表级的锁类型兼容性总结在下面的矩阵中。

X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

  如果一个锁与现有的锁兼容，就会授予请求的事务，但如果与现有的锁冲突，就不会授予。一个事务会等待，直到与之冲突的现有锁被释放。如果一个锁请求与现有的锁冲突，并且不能被授予，因为它会导致死锁，那么就会发生一个错误。

  除了全表请求（例如，LOCK TABLES … WRITE），意向锁不会阻止任何东西。意向锁的主要目的是显示有人正在锁定某一行，或将要锁定表中的某一行。

  在SHOW ENGINE INNODB STATUS和InnoDB监控输出中，意向锁的事务数据显示类似于以下内容：

1
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

Q：为什么有意向锁？

  为了提高并发锁操作的性能，啥意思呢？我们可以先讨论个问题。
  如果一个数据行已被一个线程加锁处理中了，此时再来了一个新线程，那么新线程正常情况下会怎么执行自己的后续步骤呢？
  答案很简单，尝试对数据行加锁，但是会发现数据行已被加锁，那么阻塞等待。
  那么，经过以上一个过程，我们可以再思考一个问题：如果一个数据行已被加锁，后续线程可不可以直接阻塞等待，不要去尝试对数据行加锁，反正也抢不到锁，这么做可以减少一个步骤。
  当然，可以，那么如何做呢？
  需要一个标志来标明数据行已被加锁，而意向锁就可以作为这个标志。

记录锁

  记录锁是对一个索引记录的锁。

  例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;语句可以防止任何其他事务插入、更新或删除t.c1的值为10的记录。

  记录锁总是锁定索引记录，即使一个表被定义为没有索引。在这种情况下，InnoDB 会创建一个隐藏的聚集索引，并使用这个索引来锁定记录。

  记录锁的事务数据在SHOW ENGINE INNODB STATUS和 InnoDB 监控输出中显示类似于以下内容：

1
2
3
4
5
6
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁

  间隙锁是对索引记录之间的间隙的锁，或者对第一个或最后一个索引记录之前的间隙的锁。

  例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;语句可以防止其他事务在列t.c1中插入一个15的值，无论该列中是否已经有这样的值，因为该范围中所有现有值之间的空隙被锁定。

  一个间隙可能横跨一个索引值，多个索引值，甚至是空的。

  间隙锁是性能和并发性之间权衡的一部分，并且只在一些事务隔离级别中使用。

  对于使用唯一索引锁定行以搜索唯一行的语句，不需要间隙锁。(这不包括搜索条件只包括多列的某些列的情况。

  在 locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE)，UPDATE 和 DELETE 时，锁定取决于 SQL 语句是否是使用具有唯一搜索条件的唯一索引，或者范围类型的搜索条件：

  • 对于具有唯一搜索条件的唯一索引，InnoDB 只锁定找到的索引记录，不锁定之前的间隙
  • 对于其他搜索条件，InnoDB 锁定扫描的索引范围，使用间隙锁或临键锁来阻止插入由其他 Session 填补范围内覆盖的间隙

插入意向锁

  插入意向锁是一种由 INSERT 操作在行插入前设置的间隙锁。这种锁发出了插入意向的信号，如果多个事务在同一索引间隙中插入的位置不一样，就不需要互相等待。

  假设有数值为 4 和 7 的索引记录。分别试图插入值为 5 和 6 的事务，在获得插入行的排他锁之前，各自用插入意向锁（间隙锁）锁定了 4 和 7 之间的间隙，但是由于这些行是不冲突的，所以不会互相阻塞。

  下面的例子演示了一个事务在获得被插入记录的排他锁之前，采取插入意向锁。这个例子涉及两个客户，A 和 B。

  客户端 A 创建了一个包含两个索引记录（90 和 102）的表，然后启动一个事务，对 ID 大于 100 的索引记录加了一个排他锁。该排他锁包括 102 号记录前的间隙锁：

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

  客户端 B 开始一个事务，向缺口插入一条记录。该事务在等待获得排他锁的同时，采取了一个插入意向锁。

1
2
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

  在 SHOW ENGINE INNODB STATUS 和 InnoDB 监控输出中，插入意向锁的事务数据显示类似于以下内容：

1
2
3
4
5
6
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC 锁

  AUTO-INC 锁是一个特殊的表级锁，由插入有 AUTO_INCREMENT 列的表的事务使用。在最简单的情况下，如果一个事务正在向表中插入数值，任何其他事务必须等待向该表进行自己的插入，这样第一个事务插入的行就会得到连续的主键值。

  innodb_autoinc_lock_mode变量控制用于自动增量锁定的算法。它允许你选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行交换。

表锁与行锁

  对于前面涉及的锁而言，从锁的粒度维度，可以分成表锁与行锁两大类：

类别 说明 特点
表锁 对查询的整张表加锁 开销小，加锁快；不会出现死锁；
锁定力度大，发生锁冲突概率高，并发度最低
行锁 对查询出的行加锁 开销大，加锁慢；会出现死锁；
锁定粒度小，发生锁冲突的概率低，并发度高

  当然，不同的存储引擎支持的锁粒度不同：

  • InnoDB 既支持行锁又支持表锁
  • MyISAM 只支持表锁

扩展——封锁粒度与封锁协议

  数据库中为了实现并发控制而采用封锁技术。封锁对象的大小称为封锁粒度（ Granularity ）。

  封锁的对象可以是逻辑单元，亦可以是物理单元。
  以关系数据库为例，封锁对象可以为以下逻辑单元：

  • 属性值
  • 属性值的集合
  • 元组
  • 关系
  • 索引项
  • 整个索引项
  • 甚至整个数据库

  封锁对象亦可以为以下物理单元：

  • 页（数据页或索引页）
  • 物理记录等

  在运用 X 锁和 S 锁对数据对象加锁时，还需要约定一些规则。比如何时申请 X 锁 或 S 锁、持锁时间多久、何时释放等规则，这些规则被称为为封锁协议（ Locking Protocol ）。
  对封锁方式规定的不同规则，形成了不同的封锁协议，而不同的封锁协议又对应不同的隔离级别（注意哦：封锁协议和隔离级别并不是严格对应的）。

参考

文章信息

时间 说明
2022-02-18 初稿
0%